﻿Public Class u_KhachHang
    Dim DSKH1 As DataTable = New DataTable()
    Private Sub LoadData()
        Dim kh As New KhachHangBUS
        dgvDSKH.DataSource = kh.GetKhachHang
    End Sub

    Private Sub u_KhachHang_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        DSKH1 = KhachHangBUS.Get_DSKH()
        dgvDSKH.DataSource = DSKH1
        LoadData()
    End Sub

    Private Sub btnThemKH_Click(sender As Object, e As EventArgs) Handles btnThemKH.Click
        Dim newKH As New KhachHangDTO
        newKH.MaKH = txtMaKH.Text
        newKH.TenKH = txtHoTenKH.Text
        newKH.NgaySinh = DTKhachHang.Value.Date
        newKH.DiaChi = txtDiaChiKH.Text
        newKH.SDT = txtDienThoaiKH.Text
        newKH.GioiTinh = rbNamKH.Checked
        newKH.TheVip = rbTheVIPco.Checked
        newKH.CMND = txtCMNDKH.Text
        newKH.NgayCap = NgayCap.Value.Date
        newKH.MaNV = txtMaNV.Text
        Dim khBUS As New KhachHangBUS
        khBUS.AddKhachHang(newKH)
        LoadData()
    End Sub

    Private Sub btnXoaKH_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnXoaKH.Click
        If dgvDSKH.SelectedRows.Count <> 0 Then
            If dgvDSKH.SelectedRows(0).Index <> dgvDSKH.RowCount Then
                Dim delKH As New KhachHangDTO
                delKH.STT = dgvDSKH.SelectedRows(0).Cells(0).Value
                Dim khBUS As New KhachHangBUS
                khBUS.DelKhachHang(delKH)
                LoadData()
            End If
        End If
    End Sub

    Private Sub btnCapNhatKH_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCapNhatKH.Click
        If dgvDSKH.SelectedRows.Count <> 0 Then
            If dgvDSKH.SelectedRows(0).Index <> dgvDSKH.RowCount Then
                Dim newKH As New KhachHangDTO
                newKH.STT = dgvDSKH.SelectedRows(0).Cells(0).Value
                newKH.MaKH = txtMaKH.Text
                newKH.TenKH = txtHoTenKH.Text
                newKH.NgaySinh = DTKhachHang.Value.Date
                newKH.DiaChi = txtDiaChiKH.Text
                newKH.SDT = txtDienThoaiKH.Text
                newKH.GioiTinh = rbNamKH.Checked
                newKH.TheVip = rbTheVIPco.Checked
                newKH.CMND = txtCMNDKH.Text
                newKH.NgayCap = NgayCap.Value.Date
                newKH.MaNV = txtMaNV.Text
                Dim khBUS As New KhachHangBUS
                khBUS.UpdateKhachHang(newKH)
                LoadData()
            End If
        End If
    End Sub

    Private Sub dgvDSKH_SelectionChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dgvDSKH.SelectionChanged
        If dgvDSKH.SelectedRows.Count <> 0 Then
            If dgvDSKH.SelectedRows(0).Index <> dgvDSKH.RowCount Then
                txtMaKH.Text = dgvDSKH.SelectedRows(0).Cells("MaKH").Value
                txtHoTenKH.Text = dgvDSKH.SelectedRows(0).Cells("TenKH").Value
                DTKhachHang.Value = Date.Parse(dgvDSKH.SelectedRows(0).Cells("NgaySinh").Value.ToString)
                txtDiaChiKH.Text = dgvDSKH.SelectedRows(0).Cells("DiaChi").Value
                If dgvDSKH.SelectedRows(0).Cells("GioiTinh").Value = True Then
                    rbNamKH.Checked = True
                Else
                    rbNuKH.Checked = True
                End If
                txtDienThoaiKH.Text = dgvDSKH.SelectedRows(0).Cells("SDT").Value
                txtCMNDKH.Text = dgvDSKH.SelectedRows(0).Cells("CMND").Value
                If dgvDSKH.SelectedRows(0).Cells("TheVip").Value = True Then
                    rbTheVIPco.Checked = True
                Else
                    rbTheVIPKhong.Checked = True
                End If
                'NgayCap.Value = Date.Parse(dgvDSKH.SelectedRows(0).Cells("NgayCap1").Value.ToString)
                txtMaNV.Text = dgvDSKH.SelectedRows(0).Cells("MaNV").Value
            End If
        End If
    End Sub



    Private Sub btnXuat_Click(sender As Object, e As EventArgs) Handles btnXuat.Click
        Dim SaveFileDialog1 As SaveFileDialog = New SaveFileDialog()
        SaveFileDialog1.Filter = "Export (*.xls)|*.xls"
        SaveFileDialog1.ShowDialog()
        Dim File_name As String = SaveFileDialog1.FileName
        ExcelProvider.file_name = File_name
        Try

            For i As Integer = 0 To DSKH1.Rows.Count - 1
                Dim sql As String
                sql = String.Format("INSERT INTO [Sheet1$] (STT,MaKH,TenKH,NgaySinh,GioiTinh,SDT,DiaChi,TheVip,CMND,NgayCap,MaNV) values ('{0}', '{1}','{2}','{3}', '{4}','{5}','{6}', '{7}','{8}','{9}','{10}')", DSKH1.Rows(i)(0), DSKH1.Rows(i)(1), DSKH1.Rows(i)(2), DSKH1.Rows(i)(3), DSKH1.Rows(i)(4), DSKH1.Rows(i)(5), DSKH1.Rows(i)(6), DSKH1.Rows(i)(7), DSKH1.Rows(i)(8), DSKH1.Rows(i)(9), DSKH1.Rows(i)(10))
                ExcelProvider.NonQuery(sql)
            Next
            MessageBox.Show("Xuất dữ liệu thành công")
        Catch ex As Exception
            MessageBox.Show("Xuất dữ liệu bị lỗi, bạn vui lòng kiểm tra lại cấu trúc file .xls")
        End Try
    End Sub

    Dim dt As DataTable = New DataTable()
    Private Sub btnNhap_Click(sender As Object, e As EventArgs) Handles btnNhap.Click
        MessageBox.Show("Bạn phải tạo ra file .xls có cấu như trên hình, trước khi thực hiên nếu không quá trình Import sẽ bị lỗi")

        Dim SaveFileDialog1 As SaveFileDialog = New SaveFileDialog()
        SaveFileDialog1.Filter = "Export (*.xls)|*.xls"
        SaveFileDialog1.ShowDialog()
        Dim File_name As String = SaveFileDialog1.FileName

        ExcelProvider.file_name = File_name

        Try
            Dim sql As String = "Select * From [Sheet1$]"
            dt = ExcelProvider.DataAdapter(sql)
            dgvDSKH.DataSource = dt
            Dim ma As String = ""
            Try
                For i As Integer = 0 To DSKH1.Rows.Count - 1
                    Dim kh As KhachHangDTO = New KhachHangDTO()
                    Dim t As Long = 0
                    ma = dt.Rows(i)(0).ToString()
                    kh.STT = DSKH1.Rows(i)(1).ToString()
                    kh.MaKH = DSKH1.Rows(i)(2).ToString()
                    kh.TenKH = DSKH1.Rows(i)(3).ToString()
                    kh.NgaySinh = Date.Parse(DSKH1.Rows(i)(4).ToString())
                    kh.GioiTinh = DSKH1.Rows(i)(5).ToString()
                    kh.SDT = DSKH1.Rows(i)(6).ToString()
                    kh.DiaChi = DSKH1.Rows(i)(7).ToString()
                    kh.TheVip = DSKH1.Rows(i)(8).ToString()
                    kh.CMND = DSKH1.Rows(i)(9).ToString()
                    kh.NgayCap = Date.Parse(DSKH1.Rows(i)(10).ToString())
                    kh.MaNV = DSKH1.Rows(i)(11).ToString()
                Next
                MessageBox.Show("Insert xuống cơ sở dữ liệu thành công")
                dgvDSKH.DataSource = KhachHangBUS.Get_DSKH()
            Catch ex As Exception

            End Try


        Catch ex As Exception
            MessageBox.Show("Lỗi")
        End Try

    End Sub
End Class
